﻿Imports Mysoft.Map.Application
Imports System.Data
Imports System.Xml
Imports Mysoft.Map.Data
Imports System.Data.SqlClient

Public Class CwjkMNG
    '功能：执行存储过程返回会计科目/核算项目对照表
    '参数：
    '@GetType		取数方式：会计科目(KJKM)\核算项目(HSXM)
    '@BUGUID		所属公司
    '@CwztGUID		财务账套GUID
    '@ProjGUID		项目GUID
    '@OperObject		业务类型
    '@Bz			币种
    '@KmTypeGUID	会计科目\核算项目GUID
    '@FilterStr		过滤条件
    '返回：DataTable
    Public Function GetKjkmOrHsxmDT(ByVal a_strGetType As String, ByVal a_strBUGUID As String, ByVal a_strCwztGUID As String, ByVal a_strProjGUID As String, ByVal a_strOperObject As String, ByVal a_strBz As String, ByVal a_strKmTypeGUID As String, ByVal a_strApplication As String, ByVal a_strFilterStr As String) As DataTable
        Dim strSQL As String = ""
        Dim myConnection As SqlConnection = New SqlConnection(MyDB.GetSqlConnectionString())
        Dim myCommand As SqlCommand = New SqlCommand("usp_p_CwjkGetKjkmOrHsxmDT", myConnection)
        Dim dtReturn As New DataTable
        Dim ds As DataSet = New DataSet
        Dim MyAdapter As SqlDataAdapter

        MyAdapter = New SqlDataAdapter
        myCommand.CommandType = CommandType.StoredProcedure
        MyAdapter.SelectCommand = myCommand

        Dim sqlParams As SqlParameterCollection = myCommand.Parameters

        sqlParams.Add(New SqlParameter("@GetType", SqlDbType.NVarChar, 10))
        sqlParams.Add(New SqlParameter("@BUGUID", SqlDbType.NVarChar, 100))
        sqlParams.Add(New SqlParameter("@CwztGUID", SqlDbType.NVarChar, 100))
        sqlParams.Add(New SqlParameter("@ProjGUID", SqlDbType.NVarChar, 40))
        sqlParams.Add(New SqlParameter("@OperObject", SqlDbType.NVarChar, 40))
        sqlParams.Add(New SqlParameter("@Bz", SqlDbType.NVarChar, 20))
        sqlParams.Add(New SqlParameter("@KmTypeGUID", SqlDbType.NVarChar, 100))
        sqlParams.Add(New SqlParameter("@Application", SqlDbType.NVarChar, 20))
        sqlParams.Add(New SqlParameter("@FilterStr", SqlDbType.NVarChar, 4000))

        '参数赋值
        sqlParams("@GetType").Value = a_strGetType
        sqlParams("@BUGUID").Value = a_strBUGUID
        sqlParams("@CwztGUID").Value = a_strCwztGUID
        sqlParams("@ProjGUID").Value = a_strProjGUID
        sqlParams("@OperObject").Value = a_strOperObject
        sqlParams("@Bz").Value = a_strBz
        sqlParams("@KmTypeGUID").Value = a_strKmTypeGUID
        sqlParams("@Application").Value = a_strApplication
        sqlParams("@FilterStr").Value = a_strFilterStr

        ''执行存储过程
        Try
            MyAdapter.Fill(ds, "map")
            dtReturn = ds.Tables("map")
            myConnection.Close()

        Catch exc As Exception
            strSQL = "SELECT '' AS rowid,ObjectGUID, b.ProjCode AS ObjectCode, b.ProjName AS ObjectName,a.CwztGUID,KmtypeGUID, KmCode, KmName, '' as OperObject, b.ProjGUID,a.BUGUID,1 AS entity  FROM p_CwjkKjkmMap a LEFT JOIN  p_project b ON a.ObjectGUID = b.ProjGUID  WHERE 1=2 ORDER BY b.ProjCode"
            dtReturn = MyDB.GetDataTable(strSQL)
        Finally
            If dtReturn Is Nothing Then

                strSQL = "SELECT '' AS rowid,ObjectGUID, b.ProjCode AS ObjectCode, b.ProjName AS ObjectName,a.CwztGUID,KmtypeGUID, KmCode, KmName, '' as  OperObject, b.ProjGUID,a.BUGUID,1 AS entity  FROM p_CwjkKjkmMap a LEFT JOIN  p_project b ON a.ObjectGUID = b.ProjGUID  WHERE 1=2 ORDER BY b.ProjCode"
                dtReturn = MyDB.GetDataTable(strSQL)
            End If
        End Try

        Return dtReturn

    End Function

    '功能：	返回给定汉字串的首字母串,即声母串
    '参数：	strHZ - String , 给定的汉字串
    '返回：	给定的汉字串的声母串,一律为小写
    '说明：	1. 此方法基于汉字的国标汉字库区位编码的有效性，不符合此编码的系统此函数无效！
    '       2. 若汉字串含有非汉字字符,如图形符号或ASCII码,则这些非汉字字符将保持不变.
    '举例：	str_rtn =   gf_py("中华人民共和国") 
    '       str_rtn =?'zhrmghg'
    Public Function PinYin(ByVal strHZ As String) As String

        Dim ls_ch, ls_temp As String                     '临时单元
        Dim ls_ReturnStr As String               '返回串
        Dim i, j As Integer
        Dim li_SectorCode As Integer             '汉字区码
        Dim li_PositionCode As Integer           '汉字位码
        Dim li_SecPosCode As Integer             '汉字区位码
        Dim li_offset As Integer                 '二级字库偏移量

        '存放所有国标二级汉字读音
        Dim ls_SecondSecTable As String = "CJWGNSPGCGNE[Y[BTYYZDXYKYGT[JNNJQMBSGZSCYJSYY[PGKBZGY[YWJKGKLJYWKPJQHY[W[DZLSGMRYPYWWCCKZNKYYGTTNJJNYKKZYTCJNMCYLQLYPYQFQRPZSLWBTGKJFYXJWZLTBNCXJJJJTXDTTSQZYCDXXHGCK[PHFFSS[YBGXLPPBYLL[HLXS[ZM[JHSOJNGHDZQYKLGJHSGQZHXQGKEZZWYSCSCJXYEYXADZPMDSSMZJZQJYZC[J[WQJBYZPXGZNZCPWHKXHQKMWFBPBYDTJZZKQHYLYGXFPTYJYYZPSZLFCHMQSHGMXXSXJ[[DCSBBQBEFSJYHXWGZKPYLQBGLDLCCTNMAYDDKSSNGYCSGXLYZAYBNPTSDKDYLHGYMYLCXPY[JNDQJWXQXFYYFJLEJPZRXCCQWQQSBNKYMGPLBMJRQCFLNYMYQMSQYRBCJTHZTQFRXQHXMJJCJLXQGJMSHZKBSWYEMYLTXFSYDSWLYCJQXSJNQBSCTYHBFTDCYZDJWYGHQFRXWCKQKXEBPTLPXJZSRMEBWHJLBJSLYYSMDXLCLQKXLHXJRZJMFQHXHWYWSBHTRXXGLHQHFNM[YKLDYXZPYLGG[MTCFPAJJZYLJTYANJGBJPLQGDZYQYAXBKYSECJSZNSLYZHSXLZCGHPXZHZNYTDSBCJKDLZAYFMYDLEBBGQYZKXGLDNDNYSKJSHDLYXBCGHXYPKDJMMZNGMMCLGWZSZXZJFZNMLZZTHCSYDBDLLSCDDNLKJYKJSYCJLKWHQASDKNHCSGANHDAASHTCPLCPQYBSDMPJLPZJOQLCDHJJYSPRCHN[NNLHLYYQYHWZPTCZGWWMZFFJQQQQYXACLBHKDJXDGMMYDJXZLLSYGXGKJRYWZWYCLZMSSJZLDBYD[FCXYHLXCHYZJQ[[QAGMNYXPFRKSSBJLYXYSYGLNSCMHZWWMNZJJLXXHCHSY[[TTXRYCYXBYHCSMXJSZNPWGPXXTAYBGAJCXLY[DCCWZOCWKCCSBNHCPDYZNFCYYTYCKXKYBSQKKYTQQXFCWCHCYKELZQBSQYJQCCLMTHSYWHMKTLKJLYCXWHEQQHTQH[PQ[QSCFYMNDMGBWHWLGSLLYSDLMLXPTHMJHWLJZYHZJXHTXJLHXRSWLWZJCBXMHZQXSDZPMGFCSGLSXYMJSHXPJXWMYQKSMYPLRTHBXFTPMHYXLCHLHLZYLXGSSSSTCLSLDCLRPBHZHXYYFHB[GDMYCNQQWLQHJJ[YWJZYEJJDHPBLQXTQKWHLCHQXAGTLXLJXMSL[HTZKZJECXJCJNMFBY[SFYWYBJZGNYSDZSQYRSLJPCLPWXSDWEJBJCBCNAYTWGMPAPCLYQPCLZXSBNMSGGFNZJJBZSFZYNDXHPLQKZCZWALSBCCJX[YZGWKYPSGXFZFCDKHJGXDLQFSGDSLQWZKXTMHSBGZMJZRGLYJBPMLMSXLZJQQHZYJCZYDJWBMYKLDDPMJEGXYHYLXHLQYQHKYCWCJMYYXNATJHYCCXZPCQLBZWWYTWBQCMLPMYRJCCCXFPZNZZLJPLXXYZTZLGDLDCKLYRZZGQTGJHHGJLJAXFGFJZSLCFDQZLCLGJDJCSNZLLJPJQDCCLCJXMYZFTSXGCGSBRZXJQQCTZHGYQTJQQLZXJYLYLBCYAMCSTYLPDJBYREGKLZYZHLYSZQLZNWCZCLLWJQJJJKDGJZOLBBZPPGLGHTGZXYGHZMYCNQSYCYHBHGXKAMTXYXNBSKYZZGJZLQJDFCJXDYGJQJJPMGWGJJJPKQSBGBMMCJSSCLPQPDXCDYYKY[CJDDYYGYWRHJRTGZNYQLDKLJSZZGZQZJGDYKSHPZMTLCPWNJAFYZDJCNMWESCYGLBTZCGMSSLLYXQSXSBSJSBBSGGHFJLYPMZJNLYYWDQSHZXTYYWHMZYHYWDBXBTLMSYYYFSXJC[DXXLHJHF[SXZQHFZMZCZTQCXZXRTTDJHNNYZQQMNQDMMG[YDXMJGDHCDYZBFFALLZTDLTFXMXQZDNGWQDBDCZJDXBZGSQQDDJCMBKZFFXMKDMDSYYSZCMLJDSYNSBRSKMKMPCKLGDBQTFZSWTFGGLYPLLJZHGJ[GYPZLTCSMCNBTJBQFKTHBYZGKPBBYMTDSSXTBNPDKLEYCJNYDDYKZDDHQHSDZSCTARLLTKZLGECLLKJLQJAQNBDKKGHPJTZQKSECSHALQFMMGJNLYJBBTMLYZXDCJPLDLPCQDHZYCBZSCZBZMSLJFLKRZJSNFRGJHXPDHYJYBZGDLQCSEZGXLBLGYXTWMABCHECMWYJYZLLJJYHLG[DJLSLYGKDZPZXJYYZLWCXSZFGWYYDLYHCLJSCMBJHBLYZLYCBLYDPDQYSXQZBYTDKYXJY[CNRJMPDJGKLCLJBCTBJDDBBLBLCZQRPPXJCJLZCSHLTOLJNMDDDLNGKAQHQHJGYKHEZNMSHRP[QQJCHGMFPRXHJGDYCHGHLYRZQLCYQJNZSQTKQJYMSZSWLCFQQQXYFGGYPTQWLMCRNFKKFSYYLQBMQAMMMYXCTPSHCPTXXZZSMPHPSHMCLMLDQFYQXSZYYDYJZZHQPDSZGLSTJBCKBXYQZJSGPSXQZQZRQTBDKYXZKHHGFLBCSMDLDGDZDBLZYYCXNNCSYBZBFGLZZXSWMSCCMQNJQSBDQSJTXXMBLTXZCLZSHZCXRQJGJYLXZFJPHYMZQQYDFQJJLZZNZJCDGZYGCTXMZYSCTLKPHTXHTLBJXJLXSCDQXCBBTJFQZFSLTJBTKQBXXJJLJCHCZDBZJDCZJDCPRNPQCJPFCZLCLZXZDMXMPHJSGZGSZZQLYLWTJPFSYASMCJBTZKYCWMYTCSJJLJCQLWZMALBXYFBPNLSFHTGJWEJJXXGLLJSTGSHJQLZFKCGNNNSZFDEQFHBSAQTGYLBXMMYGSZLDYDQMJJRGBJTKGDHGKBLQKBDMBYLXWCXYTTYBKMRTJZXQJBHLMHMJJZMQASLDCYXYQDLQCAFYWYXQHZ"
        '存放国标一级汉字不同读音的起始区位码对应读音
        Dim lc_FirstLetter() As Char = {"A", "B", "C", "D", "E", "F", "G", "H", "J", "K", "L", "M", "N", "O", "P", "Q", "R", "S", "T", "W", "X", "Y", "Z"}
        '存放国标一级汉字不同读音的起始区位码（共23）
        Dim li_SecPosValue() As Integer = {1601, 1637, 1833, 2078, 2274, 2302, 2433, 2594, 2787, 3106, 3212, 3472, 3635, 3722, 3730, 3858, 4027, 4086, 4390, 4558, 4684, 4925, 5249}


        ls_ReturnStr = ""
        For i = 1 To Len(strHZ)                         '依次处理as_hz中每个字符
            ls_ch = Mid(strHZ, i, 1)
            ls_temp = Encode936(ls_ch)
            If ls_temp < 128 Then                    '非汉字
                '如果是非字母，不生成对应字符，财务接口会生成表名，dbf表有特殊字符会报错
                If (ls_temp <= 122 And ls_temp >= 97) Or (ls_temp <= 90 And ls_temp >= 65) Then
                    ls_ReturnStr = ls_ReturnStr + ls_ch     '不变
                End If

            Else                                        '是汉字
                ls_ch = Mid(strHZ, i, 1)                '取出此汉字
                Dim bytes() As Byte
                bytes = System.Text.Encoding.GetEncoding(936).GetBytes(ls_ch)

                li_SectorCode = bytes(0) - 160      '区码
                li_PositionCode = bytes(1) - 160    '位码


                li_SecPosCode = li_SectorCode * 100 + li_PositionCode        ' 区位码
                If li_SecPosCode > 1600 And li_SecPosCode < 5590 Then        '  第一个字符
                    For j = 22 To 0 Step -1             '找声母
                        If li_SecPosCode >= li_SecPosValue(j) Then
                            ls_ReturnStr = ls_ReturnStr + lc_FirstLetter(j)
                            Exit For
                        End If
                    Next
                Else                                     '第一个字符
                    li_offset = (li_SectorCode - 56) * 94 + li_PositionCode - 1      ' 计算偏移量
                    If li_offset >= 0 And li_offset <= 3007 Then                     '二区汉字
                        ls_ReturnStr = ls_ReturnStr + Mid(ls_SecondSecTable, li_offset, 1)        '取出此字声母
                    End If
                End If

            End If
        Next

        Return ls_ReturnStr.ToLower()                   '返回 as_InputString 的声母串

    End Function


    '功能：返回给定汉字的高位字节值
    '参数：strChn   --汉字
    '返回：高位存储的值
    '举例： Encode936("啊") ，返回 176
    Private Function Encode936(ByVal strChn As String) As Integer
        Dim bytes() As Byte
        bytes = System.Text.Encoding.GetEncoding(936).GetBytes(strChn)
        Return bytes(0)
    End Function


    '反射调用保存的公共函数
    Public Shared Function SaveCWJK(ByVal DataXml As String, ByVal UserDataXml As String) As String
        '1、定义所需变量
        Dim xmlDOM As New XmlDocument
        Dim strEntity As String = ""
        Dim strResult As String = "<xml result=""false"" errormessage=""运行时异常，请与管理员联系！""></xml>"

        If Not DataXml Is Nothing Then

            '2、载入DataXml，并根据 DocumentElement.Name 来判断应该调用的方法
            xmlDOM.LoadXml(DataXml)

            strEntity = xmlDOM.DocumentElement.Name

            Select Case strEntity
                Case "p_CwjkProject"
                    strResult = SaveCwjkProject(DataXml, UserDataXml)               '保存财务账套的使用范围
					Exit Select
				Case "ExportCwzt"
					strResult = New CwjkZt().ExportZT(DataXml, UserDataXml)			'导出财务账套
					Exit Select
				Case "ImportCwzt"
					strResult = New CwjkZt().ImportZT(DataXml, UserDataXml)			'导入财务账套
					Exit Select
				Case "p_CwjkCwzt"
					strResult = New CwjkZt().SaveCwjkZt(DataXml, UserDataXml)		'保存财务账套
					Exit Select
				Case Else
					strResult = MyDB.SaveXml(DataXml, "XMLHTTP")					'默认情况下调用公共的 SaveXml 方法保存数据
					Exit Select
			End Select

        End If

        '3、返回执行结果
        Return strResult

    End Function

    '保存财务账套的使用范围
    Public Shared Function SaveCwjkProject(ByVal DataXml As String, ByVal UserDataXml As String) As String

        '1、定义所需变量
        Dim strRtn As String = ""
        Dim strSql As String = ""
        Dim strBUGUID As String = ""
        Dim strProjGUID As String = ""
        Dim strApplication As String = ""

        Dim xmlDOMUser As New XmlDocument

        Try

            If UserDataXml <> "" AndAlso UserDataXml <> "null" Then

                '2、载入UserDataXml，并判断参数是否传入
                xmlDOMUser.LoadXml(UserDataXml)

                Dim dataxmlNodes As XmlNodeList = xmlDOMUser.SelectNodes("/myxml/dataxml")

                If dataxmlNodes(0).Attributes.GetNamedItem("entity").InnerText = "param" Then
                    strBUGUID = dataxmlNodes(0).SelectSingleNode("BUGUID").InnerText
                    strProjGUID = dataxmlNodes(0).SelectSingleNode("ProjGUID").InnerText
                    strApplication = dataxmlNodes(0).SelectSingleNode("Application").InnerText
                End If

                If strBUGUID <> "" AndAlso strProjGUID <> "" Then

                    '3、判断该项目是否已被其它账套选中，如果已选中，则取出该账套名称
                    strSql = "SELECT Count(1) FROM p_CwjkProject as a left join p_CwjkCwzt as b on a.CwztGUID=b.CwztGUID WHERE a.BUGUID='" & strBUGUID & "' and a.ProjGUID ='" & strProjGUID & "' and b.Application='" & strApplication & "'"

                    Dim intCount As Integer = MyDB.GetDataItemInt(strSql)

                    If intCount > 0 Then
                        strSql = "SELECT CwztName FROM p_CwjkCwzt WHERE Application='" & strApplication & "' and CwztGUID in (select CwztGUID from p_CwjkProject where BUGUID='" & strBUGUID & "' and ProjGUID ='" & strProjGUID & "')"

                        Dim strCwztName As String = MyDB.GetDataItemString(strSql)

                        strRtn = "<xml result=""false"" errormessage=""该项目已经被" & strCwztName & "引用，请重新选择！""></xml>"
                    End If
                Else
                    strRtn = "<xml result=""false"" errormessage=""保存失败，请重试！""></xml>"
                End If
            End If


            '4、没有被其它账套选中，则执行保存操作
            If strRtn = "" Then
                strRtn = MyDB.SaveXml(DataXml, "XMLHTTP")
            End If

        Catch ex As Exception
            strRtn = "<xml result=""false"" errormessage=""运行时异常，请与管理员联系！""></xml>"
        End Try

        '5、返回执行结果
        Return strRtn

    End Function

    '获取账套下异常凭证数
    Public Shared Function GetErrorPzEntryCount(ByVal strCwztGUID As String) As Integer
        Dim intErrorPzCount As Integer = 0
        If strCwztGUID <> "" Then
            intErrorPzCount = MyDB.GetDataItemInt("SELECT count(*)  FROM (select DISTINCT OperTypeGUID,GroupName,PzName from vp_CwjkPzEntryRuleYC WHERE  CwztGUID = '" & strCwztGUID & "' ) a")
        End If
        Return intErrorPzCount
    End Function
End Class
